/*
 * courseEnrollmentSystem.java
 */

package csci3170;

import org.jdesktop.application.Application;
import org.jdesktop.application.SingleFrameApplication;
import java.sql.*;

/**
 * The main class of the application.
 */
public class courseEnrollmentSystem extends SingleFrameApplication {
    private Model model;
    /**
     * At startup create and show the main frame of the application.
     */
    @Override protected void startup() {
        
        show(new courseEnrollmentSystemView(this));
    }

    /**
     * This method is to initialize the specified window by injecting resources.
     * Windows shown in our application come fully initialized from the GUI
     * builder, so this additional configuration is not needed.
     */
    @Override protected void configureWindow(java.awt.Window root) {
    }

    /**
     * A convenient static getter for the application instance.
     * @return the instance of courseEnrollmentSystem
     */
    public static courseEnrollmentSystem getApplication() {
        
        return Application.getInstance(courseEnrollmentSystem.class);
    }

    /**
     * Main method launching the application.
     */
    public static void main(String[] args) {

        launch(courseEnrollmentSystem.class, args);

    }



    public  void setInput (String[] query){
        model = new Model();
        model.setInput(query);
    }

    public Result getOutput(){
       if(model.checkConnection()){
            model.process();
            return model.output();
       }else{
           Result rs = new Result();
           rs.Check = "Connection Fail!";
           return rs;
       }
    }


    public static class myConnection {
        private static Connection con;
        private myConnection(){

        }

        public static Connection getMyConnection() throws SQLException {
            if(con == null){
              con = DriverManager.getConnection("jdbc:oracle:thin:@db11.cse.cuhk.edu.hk:1521:db11", "b017", "jbrllwxb" );
            }
              return con;
        }

    }

    /* A hard code department check */
    private boolean isDepartment(String s) {
        return (s.equals("CSE") || s.equals("BBA") || s.equals("GEU") || s.equals("CSC"));
    }


    class Model {

        private String[] query;
        private Result result;
        private Connection con;
        private Statement stmt ;


        public Model(){
            result = new Result();
        }

        public void setInput(String[] query){
            
            this.query = query;
        }

        public Result output(){
             return this.result;
        }

        public void process(){
            if(query[0].contentEquals("create")){ //create all tables: query from admin interface

                String course = "CREATE TABLE course ( "
                        + "CourseID char(8),"
                         + "CourseName char(50),"
                         + "Credit int, "
                         + "Department char(3), "
                         + "PRIMARY KEY (CourseID ) ) ";
                String student = "CREATE TABLE student ( "
                        + " StudentID char(10),"
                         + "Name char(50),"
                         + "Gender char(1), "
                         + "Address char(100), "
                         + "Major char(3),"
                         + "College char(50),"
                         + "DateOfBirth date,"
                         + "Phone char(20),"
                         + "AdmissionYear int,"
                         + "PRIMARY KEY (StudentID ) ) ";   
                String TermCourse =  "CREATE TABLE TermCourse ( "
                        + " CourseID char(8),"
                         + "Year int,"
                         + "Term int, "
                         + "Professor char(50), "
                         + "Quota int,"
                         + "NumberOfEnrolledStudent int,"
                         + "PRIMARY KEY (CourseID,Year,Term), "
                         + "FOREIGN KEY (CourseID) REFERENCES course) "; //should we use foreign key???

                String Enroll =  "CREATE TABLE Enrollment ( "
                        + " CourseID char(8),"
                         + "Year int,"
                         + "Term int, "
                         + "StudentID char(10), "
                         + "Grade char(1),"
                         + "PRIMARY KEY (CourseID,Year,Term,StudentID), "
                         + "FOREIGN KEY (CourseID,Year,Term) REFERENCES TermCourse,"
                         + "FOREIGN KEY (StudentID) REFERENCES student) ";

                String Prerequisite = "CREATE TABLE Prerequisite ( "
                        + "CourseID char(8),"
                        + "PreCourseID char(8),"
                        + "PRIMARY KEY (CourseID,PreCourseID), "
                        + "FOREIGN KEY (CourseID) REFERENCES Course (CourseID),"
                        + "FOREIGN KEY (preCourseID) REFERENCES Course (CourseID)) ";
                  String q[] = {course,student,TermCourse,Enroll,Prerequisite};
                  for(int i=0;i < q.length;i++){
                       try {
                        stmt.executeUpdate(q[i]);

                    }catch(Exception x){
                        System.out.println( "Unable to create the tables "+q[i]+": "+x.toString());
                        if (x.toString().split(":")[1].contains("ORA-00955")){
                             result.Check = "Tables have already existed!"  ;
                        }else{ 
                            result.Check = "Unable to create the tables" ;
                        }
                        return;
                    }
                }
                   SampleDataReader s = new SampleDataReader();
                  if(s.start()){
                        result.Check = "true";
                  }else{
                       result.Check = "Unable to insert data!!";
                       return;
                  }

            }else if(query[0].contentEquals("delete")){ // delete all table : query from admin interface
                
                String query2 = "DROP TABLE Prerequisite";
                try {
                     stmt.execute(query2);
                    result.Check = "true";
                 }catch(Exception x){
                    System.out.println( "Unable to delete the table Prerequisite: "+x.getMessage() );
                     if(x.getMessage().contains("ORA-00942")){
                        result.Check = "Tables does not exist!";
                    }else{
                        result.Check = "Unable to delete the tables!";
                    }
                }

                 query2 = "DROP TABLE Enrollment";
                try {
                     stmt.execute(query2);
                   result.Check = "true";
                 }catch(Exception x){
                    System.out.println( "Unable to delete the table Enrollment: "+x.getMessage() );
                     if(x.getMessage().contains("ORA-00942")){
                        result.Check = "Tables does not exist!";
                    }else{
                       result.Check = "Unable to delete the tables!";
                    }
                }

                    query2 = "DROP TABLE termcourse";
                try {
                     stmt.execute(query2);
                    result.Check = "true";
                 }catch(Exception x){
                    System.out.println( "Unable to delete the table termcourse: "+x.getMessage() );
                    if(x.getMessage().contains("ORA-00942")){
                        result.Check = "Tables does not exist!";
                    }else{
                       result.Check = "Unable to delete the tables!";
                    }
                }
                    
                    query2 = "DROP TABLE course";
                try {
                     stmt.execute(query2);
                    result.Check = "course true";
                 }catch(Exception x){
                    System.out.println( "Unable to delete the table course: "+x.getMessage() );
                     if(x.getMessage().contains("ORA-00942")){
                        result.Check = "Tables does not exist!";
                    }else{
                        result.Check = "Unable to delete the tables!";
                    }
                }
                      query2 = "DROP TABLE student";
                try {
                     stmt.execute(query2);
                    result.Check = "true";
                 }catch(Exception x){
                    System.out.println( "Unable to delete the table student: "+x.getMessage() );
                     if(x.getMessage().contains("ORA-00942")){
                        result.Check = "Tables does not exist!";
                    }else{
                        result.Check = "Unable to delete the tables!";
                    }
                }
                  
            }else if (query[0].equals("drop")){
                       try {
                        int affected_row = stmt.executeUpdate(query[1]);
                        if(affected_row > 0 ){
                            stmt.executeUpdate(query[2]);
                            result.Check ="true";
                        }else{
                            result.Check = "You haven't enrolled this course!";
                            System.out.println( "You haven't enrolled this course! "+query[1]);
                            return;

                        }

                    }catch(Exception x){
                        System.out.println( "Unable to drop the coures "+query[1]+": "+x.toString());
                        result.Check = "Unable to drop the course!";
                        return;
                    }
            }else if(query[0].equals("enroll")){
                         ResultSet rs=null;

                         try {
                                rs = stmt.executeQuery(query[7]);

                             }catch(Exception x){
                                System.out.println( "Unable to enroll the coures "+query[7]+": "+x.toString());
                                result.Check = "Unable to enroll the course!";
                                return;
                            }

                         
                         try{
                            if(rs.next()){

                                 rs = stmt.executeQuery(query[1]);
                            }else {
                                result.Check = "The course does not exist!";
                                System.out.println( "The course does not exist! "+query[7]);
                                return;
                            }
                        }catch(Exception x){
                            System.out.println( "Unable to enroll the coures "+query[1]+": "+x.toString());
                            result.Check = "Unable to enroll the course!";
                            return;
                        }
                         
                        try{
                            if(rs.next()){

                                rs = stmt.executeQuery(query[2]);
                            }else {
                                result.Check = "The course is full!";
                                System.out.println( "The course is full! "+query[1]);
                                return;
                            }
                        }catch(Exception x){
                            System.out.println( "Unable to enroll the coures "+query[2]+": "+x.toString());
                            result.Check = "Unable to enroll the course!";
                            return;
                        }

                        try{
                            if(rs.next()){
                                rs = stmt.executeQuery(query[3]);
                            }else {
                                result.Check = "You have taken this course!";
                                System.out.println( "You have taken this course! "+query[2]);
                                return;
                            }
                            
                        }catch(Exception x){
                            System.out.println( "Unable to enroll the coures "+query[3]+": "+x.toString());
                            result.Check = "Unable to enroll the course!";
                            return;
                        }

                        try{
                            if(rs.next()){
                                rs = stmt.executeQuery(query[4]);
                            }else {
                                result.Check = "You are not fulfill the prerequisite!";
                                System.out.println( "You are not fulfill the prerequisite! "+query[3]);
                                return;
                            }
                        }catch(Exception x){
                            System.out.println( "Unable to enroll the coures "+query[4]+": "+x.toString());
                            result.Check = "Unable to enroll the course!";
                            return;
                        }


                        try{
                            if(rs.next()){
                                 try {
                                        int affected_row = stmt.executeUpdate(query[5]);
                                        if(affected_row > 0 ){
                                            stmt.executeUpdate(query[6]);
                                            result.Check ="true";
                                    }else{
                                        result.Check = "You can't enrolled this course!";
                                        System.out.println( "You can't enrolled this course! "+query[5]);
                                        return;

                                    }
                                }catch (SQLException e){
                                    System.out.println( "Unable to enroll the coures "+query[5]+"\n"+query[6]+"\n"+e.toString());
                                    result.Check = "Unable to enroll the course!";

                                }
                            }else {
                                result.Check = "Credits are not enough!";
                                System.out.println( "Credits are not enough! "+query[4]);
                                return;
                            }
                     }catch(Exception x){
                            System.out.println( "Unable to enroll the coures "+query[4]+": "+x.toString());
                            result.Check = "Unable to enroll the course!";
                            return;
                     }
                        

               
                
            } else {
               //   ResultSet[] result_tmp = new ResultSet[query.length];
                //Table 
                //Eval_ShowAI
                //Eval_ShowPC
                //Eval_ShowSI
//                if (query[0].equals("Eval_ShowAI")) {
//
//                }

                String rs_table = "";
               boolean PreCourses = false;
                for(String q : query) {
//                for (int j = 0; j < query.length; j++) {
//                        String q = query[j];
                

                        try {
                            //
                            // INSERT Column Name Here
                            //
                            if (q.equals("Course")) {
                                rs_table = "CourseID\tCourseName                                        \tCredit\tDepartment\n"
                                            +"------------------------------------------------------------------------------------------\n";
                                continue;
                            } else if (q.equals("TermCourse")) {
                                rs_table = "CourseID\tYear\tTerm\tProfessor                                          \tQuota\tNumberOfEnrolledStudent\n"
                                            +"-----------------------------------------------------------------------------------------------------------------------\n";
                                continue;
                            } else if (q.equals("Student")){
                                 rs_table = "StudentID\tName                                             \tGender\tAddress                                                                                               \tMajor\t\t"
                                         +"College                                           \tDateOfBirth\tPhone\t\t\tAdmissionYear\n"
                                         +"---------------------------------------------------------------------------------------------------"
                                         +"-------------------------------------------------------------------------------------------------------------"
                                            +"-------------------------------------------------------------------------------------------------------------\n";
                                 continue;
                             } else if (q.equals("Enrollment")){
                                 rs_table = "CourseID\tYear\tTerm\tStudentID\tGrade\n"
                                            +"-----------------------------------------------------\n";
                                 continue;
                             } else if (q.equals("Prerequisite")){
                                 rs_table = "CourseID\tPreCourseID\n"
                                            +"---------------------------\n";
                                 continue;
                             } else if (q.equals("Mess")){
                                 rs_table = "CourseID\tCourseName                                        \tYear\tTerm\tCredit\tDepartment\tProfessor                                         \tQuota\tEnrolledStudent\tPreRequisite\n"
                                            +"---------------------------------------------------------------------------------------------------"
                                            +"-------------------------------------------------------------------------------------------------------------\n";
                                 PreCourses = true;
                                 continue;
                            } else if (q.equals("PopularCourse")){
                                 rs_table = "CourseID\tCourseName                                        \tYear\tTerm\tCredit\tDepartment\tProfessor                                         \tQuota\tNumberOfEnrolledStudent\n"
                                            +"---------------------------------------------------------------------------------------------------"
                                            +"---------------------------------------------------------------------------------------------------\n";
                                 continue;
                            } else if (q.equals("StudentInformaiton")){
                                 rs_table = "StudentID\tName                                                \tTCredit\tGender\tAddress                                                                                               \tMajor\t\t"
                                            +"College                                           \tDateOfBirth\tPhone\t\t\tAdmissionYear\n"
                                            +"---------------------------------------------------------------------------------------------------------"
                                            +"---------------------------------------------------------------------------------------------------------"
                                            +"-----------------------------------------------------------------------------------------------------------\n";
                                 continue;
                            } else if (q.equals("EnrolledCourses")){
                                 rs_table = "CourseID\tCourseName                                        \tCredit\tDepartment\tYear\tTerm\tProfessor                                         \tQuota\tEnrolledStudent\tPrerequisite\n"
                                         +"-------------------------------------------------------------------------------------------------------------------------------------------------------"
                                         +"-------------------------------------------------------------------------------------------------------------------------------------------------------\n";
                                 PreCourses = true;
                                 continue;
                            } 

                            stmt.execute(q);
                            ResultSet rs = stmt.getResultSet();
                            
                            while(rs.next()){
                                try{
                                     int i =1;
                                     while(true){
                                           String tmp = ""+rs.getObject(i);
//                                           if (tmp.length() < 6) {
//                                               tmp += "\t";
//                                           }
                                           String r = tmp.replaceAll("\\s00:00:00\\.0", "");
                                               rs_table += ""+r+"\t"; //
                                               if (isDepartment(r)) {
                                                   rs_table += "\t";
                                               }
                                               //rs_table += ""+rs.getObject(i)+"\t"; //

                                           i++;
                                           
                                     }

                                }catch (SQLException e){
                                  //  System.out.println( "sql: "+e.toString() );

                                    rs_table += '\n';
                                }

                            }

                            System.out.println( ""+rs_table );

                            result.Set.add(rs_table);
                            rs_table = "";




                        //   result_tmp[i++]=rs;
                        }catch(Exception x){
                            System.out.println( "Unable to get the results: "+q+" " + x.getMessage() );
                            result.Check = "false";
                            return;
                        }
                  }

                    if(PreCourses){
                           
                          String[] pre_splits = result.Set.get(result.Set.size()-1).split("\n");
                          String[] coures_splits =  result.Set.get(result.Set.size()-2).split("\n");
                          for(int i=0;i < pre_splits.length ; i++){
                              for(int j =0;j< coures_splits.length;j++){
                                    if(coures_splits[j].contains(pre_splits[i].split("\t")[0])){
                                        coures_splits[j] = coures_splits[j] +"\t"+pre_splits[i].split("\t")[1];
                                    }
                              }
                          }

                           result.Set.remove(result.Set.size()-1);
                           result.Set.remove(result.Set.size()-1);
                         String  table ="";
                          for(String s : coures_splits){
                               table += s+"\n";
                          }
                           System.out.println( "testing : "+table );
                            result.Set.add(table);
                     }
                 

              result.Check = "true";
            }
        }

        public boolean checkConnection(){

            try {
                Class.forName("oracle.jdbc.driver.OracleDriver"); //Or any other driver
            }catch(Exception x){
                System.out.println( "Unable to load the driver class! "+x.getMessage() );
                return false;
            }
            if(con ==null){
                try{
                    con = DriverManager.getConnection("jdbc:oracle:thin:@db11.cse.cuhk.edu.hk:1521:db11", "b017", "jbrllwxb" );

                }catch(Exception x){
                    System.out.println( "Fail to connect to the database!! "+x.getMessage() );
                    return false;
                }
            }
            if(stmt==null){
                try{
                    stmt = con.createStatement();
                }catch (Exception x){
                      System.out.println( "Fail to create Statement!!" );
                     return false;
                }
            }
            return true;
        }
    }

}

